package com.broadvision.manage.common.tools.action.user;

import java.util.ArrayList;
import java.util.ResourceBundle;

import com.broadvision.data.client.DataManager;
import com.broadvision.data.common.TableData;
import com.broadvision.data.exceptions.DataException;
import com.dps.broadvision.common.vo.ListDataItem;

public class VisitorDataUtilities {
	
	/************************************************************************/
	public static ArrayList getUserListData(){
		try
		{
			return getListData(getListDataQuery());
		}
		catch(Exception exc){
			System.out.println("Error in getting user list data");
			return new ArrayList();
		}
	}
	/************************************************************************/
	public static ArrayList getUserTypesData(){
		try
		{
			ResourceBundle rb = ResourceBundle.getBundle("ListCategories");
			String query=getDataQuery(rb.getString("user.registration.list.user_type.name"),rb.getString("user.registration.list.user_type.path"));
			return getListData(query);
		}
		catch(Exception exc){
			System.out.println("Error in getting user list data");
			return new ArrayList();
		}
	}
	/************************************************************************/
	public static ArrayList getCorporateUserStatusData(){
		try
		{
			ResourceBundle rb = ResourceBundle.getBundle("ListCategories");
			String query=getDataQuery(rb.getString("user.registration.list.corporate_user_status.name"),rb.getString("user.registration.list.corporate_user_status.path"));
			return getListData(query);
		}
		catch(Exception exc){
			System.out.println("Error in getting user list data");
			return new ArrayList();
		}
	}
	/************************************************************************/
	public static String getDataQuery(String categoryName,String categoryPath){
		
		StringBuffer query = new StringBuffer();
		query.append("select * from ");
		query.append("( ");
		query.append("select cat.NAME,text.OID, text.TEXT_KEY, text.TEXT_VALUE,cat.PATH_NAME ");
		query.append("from BV_TEXT  text,BV_CONTENT_REF ref, BV_CATEGORY_PATH cat "); 
		query.append("where text.OID=ref.OID and ref.PARENT_OID=cat.OID ");
		query.append(") ");
		//query.append("where PATH_NAME like '/Zain Kuwait Contents/Miscellaneous/User Registration/Dropdown Lists%' ");
		query.append("where PATH_NAME in ('"+categoryPath+"') ");
		query.append(" and NAME='"+categoryName+"'");
		query.append("order by NAME,TEXT_VALUE ");
		System.out.println("User Dropdown Query=="+query.toString());
		return query.toString();
	}
	/************************************************************************/
	
	public static String getListDataQuery(String language){
		
		StringBuffer query = new StringBuffer();
		query.append("select * from ");
		query.append("( ");
		query.append("select cat.name,text.OID, text.TEXT_KEY, text.TEXT_VALUE,cat.PATH_NAME ");
		query.append("from BV_TEXT  text,BV_CONTENT_REF ref, BV_CATEGORY_PATH cat "); 
		query.append("where text.OID=ref.OID and ref.PARENT_OID=cat.OID ");
		query.append("minus ");
		query.append("select cat.name,text.OID, text.TEXT_KEY, text.TEXT_VALUE,cat.PATH_NAME ");
		query.append("from BV_TEXT  text,BV_CONTENT_REF ref, BV_CATEGORY_PATH cat ,TEXT_TRANSLATE  translation ");
		query.append("where text.OID=ref.OID and ref.PARENT_OID=cat.OID ");
		query.append("and text.OID=translation.OID ");
		query.append("and translation.TEXT_LANGUAGE = '"+language+"' ");
		query.append("union ");
		query.append("select cat.name,text.OID, text.TEXT_KEY, translation.TEXT_VALUE_TRANSLATE as TEXT_VALUE,cat.PATH_NAME ");
		query.append("from BV_TEXT  text,TEXT_TRANSLATE  translation, BV_CONTENT_REF ref, BV_CATEGORY_PATH cat  ");
		query.append("where text.OID=ref.OID and ref.PARENT_OID=cat.OID ");
		query.append("and text.OID=translation.OID ");
		query.append("and translation.TEXT_LANGUAGE = '"+language+"' ");
		query.append(") ");
		//query.append("where PATH_NAME like '/Zain Kuwait Contents/Miscellaneous/User Registration/Dropdown Lists%' ");
		query.append("where PATH_NAME in ("+getCategoryPaths()+") ");
		query.append("order by NAME,TEXT_VALUE ");
		System.out.println("User Dropdown Query=="+query.toString());
		return query.toString();
	}
	/***************************************************************/
	public static String getListDataQuery(){
		
		StringBuffer query = new StringBuffer();
		query.append("select * from ");
		query.append("( ");
		query.append("select cat.NAME,text.OID, text.TEXT_KEY, text.TEXT_VALUE,cat.PATH_NAME ");
		query.append("from BV_TEXT  text,BV_CONTENT_REF ref, BV_CATEGORY_PATH cat "); 
		query.append("where text.OID=ref.OID and ref.PARENT_OID=cat.OID ");
		/*
		query.append("minus ");
		query.append("select cat.NAME,text.OID, text.TEXT_KEY, text.TEXT_VALUE,cat.PATH_NAME ");
		query.append("from BV_TEXT  text,BV_CONTENT_REF ref, BV_CATEGORY_PATH cat ,TEXT_TRANSLATE  translation ");
		query.append("where text.OID=ref.OID and ref.PARENT_OID=cat.OID ");
		query.append("and text.OID=translation.OID ");
		query.append("and translation.TEXT_LANGUAGE = '"+language+"' ");
		query.append("union ");
		query.append("select NAME,text.OID, text.TEXT_KEY, translation.TEXT_VALUE_TRANSLATE as TEXT_VALUE,cat.PATH_NAME ");
		query.append("from BV_TEXT  text,TEXT_TRANSLATE  translation, BV_CONTENT_REF ref, BV_CATEGORY_PATH cat  ");
		query.append("where text.OID=ref.OID and ref.PARENT_OID=cat.OID ");
		query.append("and text.OID=translation.OID ");
		query.append("and translation.TEXT_LANGUAGE = '"+language+"' ");
		*/
		query.append(") ");
		//query.append("where PATH_NAME like '/Zain Kuwait Contents/Miscellaneous/User Registration/Dropdown Lists%' ");
		query.append("where PATH_NAME in ("+getCategoryPaths()+") ");
		query.append("order by NAME,TEXT_VALUE ");
		System.out.println("User Dropdown Query=="+query.toString());
		return query.toString();
	}
	/************************************************************************/
	public static String getCategoryPaths(){
		ResourceBundle rb = ResourceBundle.getBundle("ListCategories");
		StringBuffer lists = new StringBuffer();
		lists.append("'");
		lists.append(rb.getString("user.registration.list.gender.path"));
		lists.append("'");
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.education_level.path"));
		lists.append("'");
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.occupation.path"));
		lists.append("'");
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.marital_status.path"));
		lists.append("'");
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.income_range.path"));
		lists.append("'");
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.city.path"));
		lists.append("'");
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.residential_area.path"));
		lists.append("'");
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.house_hold_size.path"));
		lists.append("'");
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.hobbies.path"));
		lists.append("'");
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.magazines.path"));
		lists.append("'");
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.tv_programs.path"));
		lists.append("'");
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.language.path"));
		lists.append("'");
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.secret_question.path"));
		lists.append("'");
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.organization_type.path"));
		lists.append("'");
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.business_objective.path"));
		lists.append("'");
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.years_of_establishment.path"));
		lists.append("'");
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.number_of_employees.path"));
		lists.append("'");
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.country.path"));
		lists.append("'");
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.residential_length.path"));
		lists.append("'");
		
		//
		lists.append(",");
		lists.append("'");
		lists.append(rb.getString("user.registration.list.prefix.path"));
		lists.append("'");
		
		return lists.toString();
	}
	/*************************************************************************/
	public static ArrayList getListData(String sql) throws Exception
	   {
		  ArrayList listData = new ArrayList(); 
	      TableData resTblData = null;
	      resTblData = getDataByQuery(sql);
	      int length = resTblData == null ? 0 : resTblData.size();
	      for (int i = 0; i < length; i++) {
	    	 ListDataItem dataItem  = new ListDataItem();
	    	 dataItem.setName(resTblData.getValue(i, "NAME").getString());
	    	 dataItem.setId(resTblData.getValue(i, "OID").getString());
	    	 dataItem.setValue(resTblData.getValue(i, "TEXT_VALUE").getString());
	    	 dataItem.setKey(resTblData.getValue(i, "TEXT_KEY").getString());
	    	 System.out.println("dataItem.getValue()="+dataItem.getValue()+",dataItem.getId()="+dataItem.getId()+",dataItem.getName()="+dataItem.getName());
	    	
	    	 listData.add(dataItem);
	      }
	      return listData;
	   }//edn func
	   /**************************************************************************************/
		public static TableData getDataByQuery(String selectQuery) throws DataException
		   {
			  
		      TableData resTblData;
		      try {
		    	  DataManager dataManager = new DataManager();  
		    	  //dataManager.setCallSettings(callSettings);
		          resTblData = dataManager.getDataByQuery(null, selectQuery);
		      } catch (DataException de) {
		         System.out.println(de.toString());
		         throw de;
		      }
	
		      return resTblData;
		   }
		/************************************************************************************/
}
